package com.hbnu.mysql;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
 * @author zouting
 * @Date 2025-11-04-16:28
 *
 */public class FlowerJDBCDemo02 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // 1、获取数据库配置文件中的数据库信息
            InputStream resourceAsStream = FlowerJDBCDemo02.class.getClassLoader().getResourceAsStream("jdbc.properties");

            // 添加空值检查---出现错误
            if (resourceAsStream == null) {
                throw new RuntimeException("无法找到 jdbc.properties 配置文件，请检查文件是否存在且位于正确路径");
            }

            Properties properties = new Properties();
            properties.load(resourceAsStream);
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");

            // 2、注册驱动
            Class.forName(driver);

            // 3、获取数据库连接
            connection = DriverManager.getConnection(url, username, password);

            // 4、获取数据库操作对象
            statement = connection.createStatement();

            // 5、执行SQL语句
//            String sql = "select id, username, password,address,phone from tb_user";
//            resultSet = statement.executeQuery(sql);
//            while (resultSet.next()) {
//
//                // 字段的数据以具体类型接收，通过字段名
//                int id = resultSet.getInt("id");
//                String name = resultSet.getString("username");  // 使用字段重命名后的名字
//                String pwd = resultSet.getString("password");
//                String address = resultSet.getString("address");
//                double phone = resultSet.getDouble("phone");
//
//                System.out.println(id + "\t" + name + "\t" + pwd + "\t" + address+ "\t" + phone);
//
//            }

            // 原有的用户查询
            String userSql = "select id, username, password, address, phone from tb_user";
            ResultSet userResultSet = statement.executeQuery(userSql);
            System.out.println("=== 用户信息 ===");
            while (userResultSet.next()) {
                int id = userResultSet.getInt("id");
                String name = userResultSet.getString("username");
                String pwd = userResultSet.getString("password");
                String address = userResultSet.getString("address");
                double phone = userResultSet.getDouble("phone");

                System.out.println(id + "\t" + name + "\t" + pwd + "\t" + address + "\t" + phone);
            }

            // 鲜花表查询
            String flowerSql = "select id, fid, fname, language, price from tb_flower";
            ResultSet flowerResultSet = statement.executeQuery(flowerSql);
            System.out.println("=== 鲜花信息 ===");
            while (flowerResultSet.next()) {
                int id = flowerResultSet.getInt("id");
                String fid = flowerResultSet.getString("fid");
                String fname = flowerResultSet.getString("fname");
                String language = flowerResultSet.getString("language");
                double price = flowerResultSet.getDouble("price");

                System.out.println(id + "\t" + fid + "\t" + fname + "\t" + language + "\t" + price);


            }

            // 购物车表查询
            String cartSql = "select  cid, username,fid, quantity from tb_cart";
            ResultSet cartResultSet = statement.executeQuery(cartSql);
            System.out.println("=== 购物车信息 ===");
            while (cartResultSet.next()) {
                String cid = cartResultSet.getString("cid");
                String username1 = cartResultSet.getString("username");
                String fid = cartResultSet.getString("fid");
                int quantity = cartResultSet.getInt("quantity");

                System.out.println(cid + "\t" + username1 + "\t" + fid + "\t" + quantity);
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6、关闭资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

